/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import entities.SearchVacancy;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author MrChinh
 */
public class ListSearchVacancy {

    ConnectDB myConn = new ConnectDB();
    Connection conn;

    public List<SearchVacancy> getListSearchVacancy(String cateName, String workPlace, String salary, String exp) {
        if (!cateName.equals("none") || !workPlace.equals("none") || !salary.equals("none") || !exp.equals("none")) {
            List<SearchVacancy> listSV = new ArrayList<SearchVacancy>();
            try {
                conn = myConn.getConnect();
                Statement stmt = conn.createStatement();
                StringBuilder sql = new StringBuilder();
                sql.append("SELECT DISTINCT V.VacancyID, V.Title, C.CategoryName,V.YearOfExperience, IFCP.CompanyName, ");
                sql.append("W.workPlateName, V.Salary,V.CreatedDate, V.Deadline, W.workPlaceId ");
                sql.append("FROM (((Vacancy V inner join VacancyWorkPlace VW on V.VacancyID = VW.VacancyID) ");
                sql.append("inner join WorkingPlace W on VW.workPlaceId = W.workPlaceId) ");
                sql.append("inner join Category C on V.CategoryID = C.CategoryID ) ");
                sql.append("inner join InforCompany IFCP on IFCP.CompanyID = V.CompanyID ");
                sql.append("WHERE V.Deadline > GETDATE() ");
                if (!cateName.equals("none")) {
                    sql.append(" AND C.CategoryID like '%");
                    sql.append(cateName);
                    sql.append("%'");
                }
                if (!workPlace.equals("none")) {
                    sql.append(" AND W.workPlaceId like '%");
                    sql.append(workPlace);
                    sql.append("%' ");
                }
                if (!exp.equals("none")) {
                    int chooesYear = Integer.parseInt(exp);
                    switch (chooesYear) {
                        case 0:
                            sql.append(" AND V.YearOfExperience < 1 ");
                            break;
                        case 1:
                            sql.append(" AND V.YearOfExperience = 1 ");
                            break;
                        case 2:
                            sql.append(" AND V.YearOfExperience = 2 ");
                            break;
                        case 3:
                            sql.append(" AND V.YearOfExperience = 3 ");
                            break;
                        case 4:
                            sql.append(" AND V.YearOfExperience = 4 ");
                            break;
                        case 5:
                            sql.append(" AND V.YearOfExperience = 5 ");
                            break;
                        case 6:
                            sql.append(" AND V.YearOfExperience > 5 ");
                            break;
                    }
                }
                if (!salary.equals("none")) {
                    int chooesSalary = Integer.parseInt(salary);
                    switch (chooesSalary) {
                        case 1:
                            sql.append(" AND V.Salary < 1000000 ");
                            break;
                        case 2:
                            sql.append(" AND V.Salary between 1000000 and 5000000 ");
                            break;
                        case 3:
                            sql.append(" AND V.Salary between 5000000 and 8000000 ");
                            break;
                        case 4:
                            sql.append(" AND V.Salary between 8000000 and 10000000 ");
                            break;
                        case 5:
                            sql.append(" AND V.Salary between 10000000 and 15000000 ");
                            break;
                        case 6:
                            sql.append(" AND V.Salary > 15000000 ");
                            break;
                    }
                }

                String strSql = sql.toString();
                ResultSet rs = stmt.executeQuery(strSql);
                while (rs.next()) {
                    SearchVacancy sv = new SearchVacancy();
                    sv.setVacancyId(rs.getString(1));
                    sv.setVacancyTitle(rs.getString(2));
                    sv.setCategoryName(rs.getString(3));
                    sv.setYearOfExperience(rs.getInt(4));
                    sv.setCompanyName(rs.getString(5));
                    sv.setWorkPlace(rs.getString(6));
                    sv.setSalary(rs.getString(7));
//                sv.setCreateDate(rs.getDate(8).toString());
                    Date dateC = rs.getDate(8);
                    SimpleDateFormat fd = new SimpleDateFormat("dd/MM/yy");
                    sv.setCreateDate(fd.format(dateC));
                    Date dateL = rs.getDate(9);
                    sv.setDeadline(fd.format(dateL));
                    sv.setWorkPlaceId(rs.getString("workPlaceId"));
                    listSV.add(sv);
                }
            } catch (Exception e) {
            }
            return listSV;
        } else {
            return null;
        }
    }

    public List<SearchVacancy> getlistHot() {
        List<SearchVacancy> listSV = new ArrayList<SearchVacancy>();
        try {
            conn = myConn.getConnect();
            Statement stmt = conn.createStatement();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT DISTINCT V.VacancyID, V.Title, C.CategoryName,V.YearOfExperience, IFCP.CompanyName, ");
            sql.append("W.workPlateName, V.Salary,V.CreatedDate, V.Deadline,W.workPlaceId ");
            sql.append("FROM (((Vacancy V inner join VacancyWorkPlace VW on V.VacancyID = VW.VacancyID) ");
            sql.append("inner join WorkingPlace W on VW.workPlaceId = W.workPlaceId) ");
            sql.append("inner join Category C on V.CategoryID = C.CategoryID ) ");
            sql.append("inner join InforCompany IFCP on IFCP.CompanyID = V.CompanyID ");
            sql.append("WHERE V.Deadline > GETDATE() AND V.status=1");
            String strSql = sql.toString();
            ResultSet rs = stmt.executeQuery(strSql);
            while (rs.next()) {
                SearchVacancy sv = new SearchVacancy();
                sv.setVacancyId(rs.getString(1));
                sv.setVacancyTitle(rs.getString(2));
                sv.setCategoryName(rs.getString(3));
                sv.setYearOfExperience(rs.getInt(4));
                sv.setCompanyName(rs.getString(5));
                sv.setWorkPlace(rs.getString(6));
                sv.setSalary(rs.getString(7));
//                sv.setCreateDate(rs.getDate(8).toString());
                Date dateC = rs.getDate(8);
                SimpleDateFormat fd = new SimpleDateFormat("dd/MM/yy");
                sv.setCreateDate(fd.format(dateC));
                Date dateL = rs.getDate(9);
                sv.setDeadline(fd.format(dateL));
                sv.setWorkPlaceId(rs.getString("workPlaceId"));
                listSV.add(sv);
            }

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return listSV;
    }
}
